use ATE
go

--
IF OBJECT_ID ( 'TestModifyLog_Insert', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestModifyLog_Insert;
GO
CREATE PROCEDURE TestModifyLog_Insert
	@TestID uniqueidentifier,
    @PreProductSN nvarchar(50),
	@PreTestTime datetime,
	@PreProductTypeID nvarchar(50),
	@PreUserID nvarchar(20),
	@PreENAID nvarchar(50),
	@PreIsPass nvarchar(10),
	@PreIsSwitch nvarchar(10),	
	@PostProductSN nvarchar(50),
	@PostTestTime datetime,
	@PostProductTypeID nvarchar(50),
	@PostUserID nvarchar(20),
	@PostENAID nvarchar(50),
	@PostIsPass nvarchar(10),
	@PostIsSwitch nvarchar(10),
	@ModifyTime datetime
AS    
    SET NOCOUNT ON;
    insert into TestModifyLog(TestID, PreProductSN,PreTestTime,PreProductTypeID,PreUserID,PreENAID,PreIsPass,PreIsSwitch,PostProductSN,PostTestTime,PostProductTypeID,PostUserID,PostENAID,PostIsPass,PostIsSwitch,ModifyTime)
	Values(@TestID, @PreProductSN,@PreTestTime,@PreProductTypeID,@PreUserID,@PreENAID,@PreIsPass,@PreIsSwitch,@PostProductSN,@PostTestTime,@PostProductTypeID,@PostUserID,@PostENAID,@PostIsPass,@PostIsSwitch,@ModifyTime);	
GO


--
IF OBJECT_ID ( 'TestModifyLog_select', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestModifyLog_select;
GO
CREATE PROCEDURE TestModifyLog_select
AS    
    SET NOCOUNT ON;
    select TestID, PreProductSN,PreTestTime,PreProductTypeID,PreUserID,PreENAID,PreIsPass,PreIsSwitch,PostProductSN,PostTestTime,PostProductTypeID,PostUserID,PostENAID,PostIsPass,PostIsSwitch,ModifyTime
	from TestModifyLog 
	order by ModifyTime desc;	
GO

--
--IF OBJECT_ID ( 'TestModifyLog_selectbySNDT', 'P' ) IS NOT NULL 
--    DROP PROCEDURE TestModifyLog_selectbySNDT;
--GO
--CREATE PROCEDURE TestModifyLog_selectbySNDT
--	@ProductSN nvarchar(50),
--	@TestTime datetime
--AS    
--    SET NOCOUNT ON;
--    select PreProductSN,PreTestTime,PreProductTypeID,PreUserID,PreENAID,PreIsPass,PreIsSwitch,PostProductSN,PostTestTime,PostProductTypeID,PostUserID,PostENAID,PostIsPass,PostIsSwitch,ModifyTime
--	from TestModifyLog
--	where PostProductSN=@ProductSN and PostTestTime=@TestTime;	
--GO




IF OBJECT_ID ('TestModifyLog_TRUpdate','TR') IS NOT NULL
   DROP TRIGGER TestModifyLog_TRUpdate
GO
CREATE TRIGGER TestModifyLog_TRUpdate ON Test
AFTER update
AS
insert into TestModifyLog(TestID,PreProductSN,PreTestTime,PreProductTypeID,PreUserID,PreENAID,PreIsPass,PreIsSwitch,PostProductSN
			,PostTestTime,PostProductTypeID,PostUserID,PostENAID,PostIsPass,PostIsSwitch,ModifyTime)
			select d.TestID,d.ProductSN,d.TestTime,d.ProductTypeID,d.UserID,d.ENAID,d.IsPass,d.IsSwitch,i.ProductSN,i.TestTime
			,i.ProductTypeID,i.UserID,i.ENAID,i.IsPass,i.IsSwitch,getdate() 
			from deleted d inner join inserted i on d.TestID=i.TestID
GO